-- exec SP_CREATE_UPDATE_ADDRESS_SITIME 'TEST1', 'SHIPADDRESS','S_Address1','S_Address2', 'S_Address3', 'COUNTRY', 'S_City', 'S_State','94086','7','8'

ALTER PROC SP_CREATE_UPDATE_ADDRESS_SITIME
@CUSTOMER_NUMBER   	VARCHAR( 50 ),
@TAG_NAME    		VARCHAR( 50 ),
@ADDRESS_1			VARCHAR( 61 ),
@ADDRESS_2			VARCHAR( 61 ),
@ADDRESS_3          VARCHAR( 61 ),
@COUNTRY            VARCHAR( 61 ),
@CITY				VARCHAR( 50 ),
@STATE	            VARCHAR( 50 ),
@ZIP	            VARCHAR( 50 ),
@PHONE	            VARCHAR( 50 ),
@FAX	            VARCHAR( 50 ),
@CONTACTPERSON		VARCHAR( 50)
as
begin
SET NOCOUNT ON
	DECLARE @ADDRESS_ID INT
	DECLARE @CUSTOMER_ID INT
	DECLARE @CUSTOMER_NAME VARCHAR(50)
	DECLARE @CONTACT_EXISTS INT

	SELECT @CUSTOMER_ID = Customer_ID,  @CUSTOMER_NAME = C_NAME FROM CUSTOMER WHERE C_Number = @CUSTOMER_NUMBER  AND C_Disabled = 1

	IF @CUSTOMER_ID IS NOT NULL AND @CUSTOMER_ID > 1
	BEGIN
		/*SELECT @ADDRESS_ID = CSAT.ADDRESS_ID FROM Customer_Shipping_Address_Table CSAT, CUSTOMER C, ADDRESS A WHERE CSAT.Customer_ID = C.Customer_ID AND CSAT.Address_ID = A.ADDRESS_ID 
		AND A.TAG_NAME = @TAG_NAME AND C.C_NUMBER = @CUSTOMER_NUMBER*/

		SELECT @ADDRESS_ID = ADDRESS_ID FROM ADDRESS WHERE ADDRESS_ID IN ( SELECT C_Default_Shipping_Address_ID FROM CUSTOMER WHERE CUSTOMER_ID = @CUSTOMER_ID UNION SELECT ADDRESS_ID FROM Customer_Shipping_Address_Table WHERE CUSTOMER_ID = @CUSTOMER_ID ) AND TAG_NAME = @TAG_NAME

		IF @ADDRESS_ID IS NULL OR @ADDRESS_ID < 1
		BEGIN
			INSERT INTO ADDRESS ( ADDRESS_1, ADDRESS_2, ADDRESS_3, COUNTRY, CITY, STATE, ZIPCODE, PHONE_NUMBER, FAX_NUMBER, CONTACT_PERSON, TAG_NAME )
			VALUES( @ADDRESS_1, @ADDRESS_2, @ADDRESS_3, @COUNTRY, @CITY, @STATE,@ZIP, @PHONE, @FAX, @CONTACTPERSON, @TAG_NAME  )
			SELECT @ADDRESS_ID = @@IDENTITY
			
			INSERT INTO Customer_Shipping_Address_Table VALUES ( @CUSTOMER_ID, @ADDRESS_ID, CURRENT_TIMESTAMP )
		END
		ELSE
		BEGIN
			UPDATE ADDRESS SET ADDRESS_1 = @ADDRESS_1, ADDRESS_2 = @ADDRESS_2, ADDRESS_3 = @ADDRESS_3, 
			COUNTRY = @COUNTRY, CITY = @CITY, STATE = @STATE, ZIPCODE = @ZIP, PHONE_NUMBER = @PHONE, FAX_NUMBER =  @FAX, CONTACT_PERSON = @CONTACTPERSON
			WHERE Address_ID = @ADDRESS_ID
		END
	
		SET @CONTACT_EXISTS = 0
		IF @CONTACTPERSON IS NOT NULL AND LEN(LTRIM(RTRIM(@CONTACTPERSON))) > 0
		BEGIN
			SELECT @CONTACT_EXISTS = COUNT(*) FROM Address_Contact_Persons_Table WHERE Address_ID = @ADDRESS_ID and CONTACT_PERSON = @CONTACTPERSON
			IF @CONTACT_EXISTS IS NOT NULL AND @CONTACT_EXISTS > 0
			BEGIN
				UPDATE Address_Contact_Persons_Table SET PHONE_NUMBER = @PHONE, FAX = @FAX WHERE Address_ID = @ADDRESS_ID and CONTACT_PERSON = @CONTACTPERSON
			END
			ELSE
			BEGIN
				INSERT INTO Address_Contact_Persons_Table ( ADDRESS_ID, CONTACT_PERSON, PHONE_NUMBER, FAX ) VALUES ( @ADDRESS_ID, @CONTACTPERSON,@PHONE, @FAX  )
			END
		END

		SET @ADDRESS_ID = -1


		SELECT @ADDRESS_ID = ADDRESS_ID FROM ADDRESS WHERE ADDRESS_ID IN ( SELECT C_Default_Billing_Address_ID FROM CUSTOMER WHERE CUSTOMER_ID = @CUSTOMER_ID UNION SELECT ADDRESS_ID FROM CUSTOMER_BILLING_ADDRESS_TABLE WHERE CUSTOMER_ID = @CUSTOMER_ID ) AND TAG_NAME = @TAG_NAME
		/*SELECT @ADDRESS_ID = CBAT.ADDRESS_ID FROM CUSTOMER_BILLING_ADDRESS_TABLE CBAT, CUSTOMER C, ADDRESS A WHERE CBAT.Customer_ID = C.Customer_ID AND CBAT.Address_ID = A.ADDRESS_ID 
		AND A.TAG_NAME = @TAG_NAME AND C.C_NUMBER = @CUSTOMER_NUMBER*/

		IF @ADDRESS_ID IS NULL OR @ADDRESS_ID < 1
		BEGIN
			INSERT INTO ADDRESS ( ADDRESS_1, ADDRESS_2, ADDRESS_3, COUNTRY, CITY, STATE, ZIPCODE, PHONE_NUMBER, FAX_NUMBER, TAG_NAME, CONTACT_PERSON )
			VALUES( @ADDRESS_1, @ADDRESS_2, @ADDRESS_3, @COUNTRY, @CITY, @STATE,@ZIP, @PHONE, @FAX, @TAG_NAME, @CONTACTPERSON )
			SELECT @ADDRESS_ID = @@IDENTITY
			
			INSERT INTO CUSTOMER_BILLING_ADDRESS_TABLE VALUES( @CUSTOMER_ID, @ADDRESS_ID, CURRENT_TIMESTAMP )
		END
		ELSE
		BEGIN
			UPDATE ADDRESS SET ADDRESS_1 = @ADDRESS_1, ADDRESS_2 = @ADDRESS_2, ADDRESS_3 = @ADDRESS_3, 
			COUNTRY = @COUNTRY, CITY = @CITY, STATE = @STATE, ZIPCODE = @ZIP, PHONE_NUMBER = @PHONE, FAX_NUMBER =  @FAX, CONTACT_PERSON = @CONTACTPERSON
			WHERE Address_ID = @ADDRESS_ID
		END

		SET @CONTACT_EXISTS = 0
		IF @CONTACTPERSON IS NOT NULL AND LEN(LTRIM(RTRIM(@CONTACTPERSON))) > 0
		BEGIN
			SELECT @CONTACT_EXISTS = COUNT(*) FROM Address_Contact_Persons_Table WHERE Address_ID = @ADDRESS_ID and CONTACT_PERSON = @CONTACTPERSON
			IF @CONTACT_EXISTS IS NOT NULL AND @CONTACT_EXISTS > 0
			BEGIN
				UPDATE Address_Contact_Persons_Table SET PHONE_NUMBER = @PHONE, FAX = @FAX WHERE Address_ID = @ADDRESS_ID and CONTACT_PERSON = @CONTACTPERSON
			END
			ELSE
			BEGIN
				INSERT INTO Address_Contact_Persons_Table ( ADDRESS_ID, CONTACT_PERSON, PHONE_NUMBER, FAX ) VALUES ( @ADDRESS_ID, @CONTACTPERSON,@PHONE, @FAX  )
			END
		END
	END
END